﻿using System;
using System.Data;
using System.Web;
using System.IO;

using System.Threading;
using System.Data.OleDb;

/// <summary>
/// DataTableToExcel 的摘要说明
/// </summary>
public class DataTableToExcel
{
    /// <summary>
    /// 由 DataSet 导出 Excel 
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="FileName"></param>       
    public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName)
    {
        HttpResponse resp = HttpContext.Current.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
        string colHeaders = "", ls_item = "";
        int i = 0;

        DataRow[] myRow = dt.Select("");

        //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
        for (i = 0; i < dt.Columns.Count; i++)
        {
            if (i == dt.Columns.Count - 1)
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "\n";
            }
            else
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "\t";
            }
        }
        resp.Write(colHeaders);

        foreach (DataRow row in myRow)
        {
            for (i = 0; i < dt.Columns.Count; i++)
            {
                if (i == dt.Columns.Count - 1)
                {
                    ls_item += row[i].ToString() + "\n";
                }
                else
                {
                    ls_item += row[i].ToString() + "\t";
                }
            }

            resp.Write(ls_item);
            ls_item = "";
        }

        resp.End();
    }

    /// <summary>
    /// 由 DataSet 导出 Excel ，自定义字段名 
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="FileName"></param>       
    public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName, string[] cells)
    {
        HttpResponse resp = HttpContext.Current.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
        string colHeaders = "", ls_item = "";
        int i = 0;

        DataRow[] myRow = dt.Select("");

        //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
        for (i = 0; i < cells.Length; i++)
        {
            if (i == cells.Length - 1)
            {
                colHeaders += cells[i].ToString() + "\n";
            }
            else
            {
                colHeaders += cells[i].ToString() + "\t";
            }
        }
        resp.Write(colHeaders);

        foreach (DataRow row in myRow)
        {
            for (i = 0; i < dt.Columns.Count; i++)
            {
                if (i == dt.Columns.Count - 1)
                {
                    ls_item += row[i].ToString() + "\n";
                }
                else
                {
                    ls_item += row[i].ToString() + "\t";
                }
            }

            resp.Write(ls_item);
            ls_item = "";
        }

        resp.End();
    }

    /// <summary>
    /// 下载服务器端的文件到本地
    /// </summary>
    /// <param name="_Request"> </param>
    /// <param name="_Response"> </param>
    /// <param name="_fileName"> 目的文件名称 </param>
    /// <param name="_fullPath"> 源文件路径 </param>
    /// <param name="_speed"> 速度大小(1024000 -> 10k/s) </param>        
    public static bool DownloadFile(HttpRequest Request, HttpResponse Response, string fileName, string fullPath, long speed)
    {
        try
        {
            FileStream myFile = new FileStream(fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            BinaryReader br = new BinaryReader(myFile);
            try
            {
                Response.AddHeader("Accept-Ranges", "bytes");
                Response.Buffer = false;
                long fileLength = myFile.Length;
                long startBytes = 0;

                double pack = 10240; //10K bytes
                //int sleep = 200;   //每秒5次   即5*10K bytes每秒
                int sleep = (int)Math.Floor(1000 * pack / speed) + 1;
                if (Request.Headers["Range"] != null)
                {
                    Response.StatusCode = 206;
                    string[] range = Request.Headers["Range"].Split(new char[] { '=', '-' });
                    startBytes = Convert.ToInt64(range[1]);
                }
                Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
                if (startBytes != 0)
                {
                    //Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength-1, fileLength));
                }
                Response.AddHeader("Connection", "Keep-Alive");
                Response.ContentType = "application/octet-stream";
                Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.GetEncoding("GB2312")));

                br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
                int maxCount = (int)Math.Floor((fileLength - startBytes) / pack) + 1;

                for (int i = 0; i < maxCount; i++)
                {
                    if (Response.IsClientConnected)
                    {
                        Response.BinaryWrite(br.ReadBytes(int.Parse(pack.ToString())));
                        Thread.Sleep(sleep);
                    }
                    else
                    {
                        i = maxCount;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                br.Close();

                myFile.Close();
            }
        }
        catch
        {
            return false;
        }
        return true;
    }


    //*********************** Excel 导入功能 ***************************//

    /// <summary> 
    /// 读取Excel文档中的数据到内存中 
    /// </summary> 
    /// <param name="Path"> 文件名称 </param> 
    /// <returns> 返回一个数据集 </returns> 
    public static DataSet ExcelToDataSet(string Path)
    {
        DataSet ds = new DataSet();

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        string strExcel = "";
        OleDbDataAdapter myCommand = null;

        strExcel = "select * from [Sheet1$]";
        myCommand = new OleDbDataAdapter(strExcel, strConn);
        myCommand.Fill(ds);

        return ds;
    }

    /// <summary>
    /// 将DataSet中的数据插入到SqlServer中
    /// </summary>
    /// <param name="ds"> 结果集 </param>
    /// <param name="TableName"> 表名 </param>
    /// <param name="MasterID"> 订单主表ID </param>
    public static void DataSetToSqlServer(DataSet ds, string TableName, string MasterID)
    {
        DataTable dt = new DataTable();
        long ID = ShoveEL._Convert.StrToLong(MasterID, 0);     //主表ID

        if (ds != null && ds.Tables.Count > 0)
        {
            dt = ds.Tables[0];

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string GoodName = dt.Rows[i][0].ToString();
                string GoodNo = dt.Rows[i][1].ToString();
                string Password = dt.Rows[i][2].ToString();

                //int Amount = ShoveEL._Convert.StrToInt(dt.Rows[i][2].ToString(), 0);

                DAL.Tables.T_Goods.Insert(GoodName, true, 0, true, "", true, "", true, "", true, "", true, "", true, 1, true, GoodNo, true, Password, true, 0, true);
            }
        }
    }

    /// <summary>
    /// 将DataSet中的数据插入到SqlServer中
    /// </summary>
    /// <param name="ds"> 结果集 </param>
    /// <param name="TableName"> 表名 </param>
    /// <param name="MasterID"> 订单主表ID </param>
    public static void DataSetToSqlServer2(DataSet ds, string TableName, string MasterID, int GoodTypeID)
    {
        DataTable dt = new DataTable();
        long ID = ShoveEL._Convert.StrToLong(MasterID, 0);     //主表ID

        if (ds != null && ds.Tables.Count > 0)
        {
            dt = ds.Tables[0];

            for (int i = 0; i < dt.Rows.Count; i++)
            {                
                string GoodName = dt.Rows[i][0].ToString();                
                string GoodNo = dt.Rows[i][1].ToString();
                string Password = dt.Rows[i][2].ToString();
                double Price = ShoveEL._Convert.StrToDouble(dt.Rows[i][3].ToString(),0);
                string GoodNorm = dt.Rows[i][4].ToString();
                string GoodUnit = dt.Rows[i][5].ToString();

                DAL.Tables.T_Goods.Insert(GoodName, true, Price, true, GoodNorm, true, GoodUnit, true, "", true, "", true, "", true, GoodTypeID, true, GoodNo, true, Password, true, 0, true);
            }
        }
    }

}
